Multi-Table Update
This lesson shows how we can update several tables at the same time.
We'll cover the following
Multi-Table Update#
We have an equivalent of updating multiple tables just as we can delete from multiple tables.
Syntax#
UPDATE T1, T2
SET col1 = newVal1, col2 = newVal2
WHERE <condition1>
Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/37lesson.sh and wait for the MySQL prompt to start-up.
-
Let’s say we want to write an update query that converts the FirstName and SecondName strings stored in the Actors table to upper case for those actors who are on Facebook, and at the same time we also want to convert the associated Facebook URL to uppercase. We can update rows in both the tables using a multi update query as follows:
UPDATE
Actors INNER JOIN DigitalAssets
ON Id = ActorId
SET FirstName = UPPER(FirstName), SecondName = UPPER(SecondName), URL = UPPER(URL)
WHERE AssetType = "Facebook";
We performed an inner join to find those actors with Facebook presence. The matching rows from both the tables get updated as observed in the screen-shot. Instead of using an inner join, we can write the same query using the WHERE clause as follows:
UPDATE Actors, DigitalAssets
SET FirstName = UPPER(FirstName), SecondName = UPPER(SecondName), URL = UPPER(URL)
WHERE AssetType = "Facebook"
AND ActorId = Id;
-
Similarly to multi delete, we can’t update a table that is also being read from in a subquery.
-
ORDER BY and LIMIT clauses can’t be used with multi table deletes.
